package net.betou.cms.dao.assist.impl;

import java.util.ArrayList;
import java.util.List;

import net.betou.cms.dao.assist.CmsDataDao;
import net.betou.cms.entity.back.CmsConstraints;
import net.betou.cms.entity.back.CmsField;
import net.betou.cms.entity.back.CmsTable;

import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;

@Repository
public class CmsDataDaoImpl extends JdbcDaoSupport implements CmsDataDao{
	private String db;

	public List< CmsTable > listTables( ) {
		String sql = "select table_name,table_comment,engine,table_rows,auto_increment from tables where table_schema='" + db + "'";
		List< CmsTable > tables = new ArrayList< CmsTable >( );
		SqlRowSet set = getJdbcTemplate( ).queryForRowSet( sql );
		while( set.next( ) ){
			CmsTable table = new CmsTable( );
			table.setName( set.getString( 1 ) );
			table.setComment( set.getString( 2 ).split( ";" )[ 0 ] );
			table.setEngine( set.getString( 3 ) );
			table.setRows( set.getInt( 4 ) );
			table.setAuto_increment( set.getInt( 5 ) );
			tables.add( table );
		}
		return tables;
	}

	public CmsTable findTable( String tablename ) {
		String sql = "select table_name,table_comment,engine,table_rows,auto_increment from tables where table_schema='" + db + "' and table_name='" + tablename + "'";
		List< CmsTable > tables = new ArrayList< CmsTable >( );
		SqlRowSet set = getJdbcTemplate( ).queryForRowSet( sql );
		while( set.next( ) ){
			CmsTable table = new CmsTable( );
			table.setName( set.getString( 1 ) );
			table.setComment( set.getString( 2 ).split( ";" )[ 0 ] );
			table.setEngine( set.getString( 3 ) );
			table.setRows( set.getInt( 4 ) );
			table.setAuto_increment( set.getInt( 5 ) );
			tables.add( table );
		}
		if( tables.size( ) > 0 ){
			return tables.get( 0 );
		}else{
			return null;
		}
	}

	public List< CmsField > listFields( String tablename ) {
		String sql = "select column_name,column_type,column_default,column_key,column_comment,is_nullable,extra from columns where table_schema='" + db + "' and table_name='" + tablename + "'";
		List< CmsField > fields = new ArrayList< CmsField >( );
		SqlRowSet set = getJdbcTemplate( ).queryForRowSet( sql );
		while( set.next( ) ){
			CmsField field = new CmsField( );
			field.setName( set.getString( 1 ) );
			field.setFieldType( set.getString( 2 ) );
			field.setFieldDefault( set.getString( 3 ) );
			field.setFieldProperty( set.getString( 4 ) );
			field.setComment( set.getString( 5 ) );
			field.setNullable( set.getString( 6 ) );
			field.setExtra( set.getString( 7 ) );
			fields.add( field );
		}
		return fields;
	}

	public List< CmsConstraints > listConstraints( String tablename ) {
		String sql = "select constraint_name,table_name,column_name,referenced_table_name,referenced_column_name from information_schema.KEY_COLUMN_USAGE where constraint_schema='" + db + "' and table_name='" + tablename + "'";
		List< CmsConstraints > constraints = new ArrayList< CmsConstraints >( );
		SqlRowSet set = getJdbcTemplate( ).queryForRowSet( sql );
		while( set.next( ) ){
			CmsConstraints constraint = new CmsConstraints( );
			constraint.setName( set.getString( 1 ) );
			constraint.setTableName( set.getString( 2 ) );
			constraint.setColumnName( set.getString( 3 ) );
			constraint.setReferencedTableName( set.getString( 4 ) );
			constraint.setReferencedColumnName( set.getString( 5 ) );
			constraints.add( constraint );
		}
		return constraints;
	}

	public String getDb( ) {
		return db;
	}

	public void setDb( String db ) {
		this.db = db;
	}

}